import abc
import logging
import uuid
from uuid import uuid4, UUID
from typing import Generic, TypeVar, Type, List, Union, Optional, Dict
from fastapi import HTTPException
from fastapi.encoders import jsonable_encoder

from sqlalchemy import func, insert, update, delete, desc, text
from sqlalchemy.engine import Result, ChunkedIteratorResult, CursorResult
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.future import select
from sqlalchemy.orm import InstrumentedAttribute
from typing import Generic, TypeVar

from afast_core.core_db.sqlalchemy.sqlalchemy_modelbase import Base
from afast_core.core_db.sqlalchemy.schema import BaseSchema, BasePaginatedSchema

# 输入模型
IN_SCHEMA = TypeVar("IN_SCHEMA", bound=BaseSchema)
SCHEMA = TypeVar("SCHEMA", bound=BaseSchema)
# 更新模式
UPDATE_SCHEMA = TypeVar("UPDATE_SCHEMA", bound=BaseSchema)
# 分页你先
PAGINATED_SCHEMA = TypeVar("PAGINATED_SCHEMA", bound=BasePaginatedSchema)
# ORM数据库模型类型
ModelType = TypeVar("ModelType", bound=Base)

logger = logging.getLogger(__name__)


class CRUDBase(
    Generic[ModelType, IN_SCHEMA, UPDATE_SCHEMA, SCHEMA, PAGINATED_SCHEMA],
    metaclass=abc.ABCMeta,
):
    def __init__(self, db_session: AsyncSession) -> None:
        self._db_session: AsyncSession = db_session

    @property
    @abc.abstractmethod
    def _model(self) -> Type[ModelType]:
        ...

    @property
    @abc.abstractmethod
    def _schema(self) -> Type[SCHEMA]:
        ...

    @property
    @abc.abstractmethod
    def _order_by(self) -> InstrumentedAttribute:
        ...

    @property
    @abc.abstractmethod
    def _paginated_schema(self) -> Type[PAGINATED_SCHEMA]:
        ...

    async def commit_session(self):
        """
        Commits the session if not in testing environment
        :return: None
        """
        await self._db_session.commit()

    async def execute_sql(self, query_sql: str, values: Dict, session: AsyncSession):
        #  单个查询==============
        # query = "SELECT * FROM users WHERE id=:id AND user_status=1"
        # values = {"id": id}
        # result = await session.execute(text(query_sql), values)
        # result = result.fetchone()
        # return UserSchema(**result)
        # 插入===================
        # query = """
        #         INSERT INTO users (
        #           first_name,
        #           last_name
        #         )
        #         VALUES (
        #           :first_name,
        #           :last_name
        #         )
        #     """
        # values = {
        #     "first_name": user.first_name,
        #     "last_name": user.last_name,
        # }
        # 查询=============================
        # query = """
        #       UPDATE
        #         users
        #       SET
        #         chat_status = :chat_status,
        #         modified_date = :modified_date
        #       WHERE
        #         user_status = 1
        #         AND email = :email
        #   """
        # values = {
        #     "chat_status": chat_status,
        #     "email": currentUser.email,
        #     "modified_date": datetime.datetime.utcnow(),
        # }
        # 删除=-==========
        # query = """
        #         DELETE
        #         FROM
        #           members
        #         WHERE
        #           roomas = :roomas
        #         AND
        #           membersid = :membersid
        #     """
        # values = {"roomas": room_id, "membersid": user_id}

        result = await session.execute(text(query_sql), values)
        return result

    async def create(self, in_schema: IN_SCHEMA) -> SCHEMA:
        entry = self._model(id=uuid4(), **in_schema.dict())
        self._db_session.add(entry)
        await self._db_session.flush()
        # 从模型中返回
        return self._schema.from_orm(entry)

    async def create_postgresql_one(self, *, session: AsyncSession, in_schema: IN_SCHEMA) -> ModelType:
        '''
        创建单个记录
        :param session: 会话对象要穿件的模型类CreateSchemaType
        :param obj:
        :return:
        '''
        # 通过jsonable_encoder转换为字典类型数据
        obj_in_data = jsonable_encoder(obj=in_schema, exclude_unset=True, by_alias=False)
        # 插入数据操作-returning仅限于POSTGRES数据库的操作
        # 生成SQL语句
        insert_statement = insert(self._model).values(**obj_in_data).returning(self._model)
        # 创建查询
        statement = select(self._model).from_statement(insert_statement).execution_options(populate_existing=True)
        # 执行原生SQL语句
        result: ChunkedIteratorResult = await session.execute(statement=statement)
        # 执行提交会话处理
        await session.commit()
        # 返回单个模型对象
        data: ModelType = result.scalar_one()
        return data

    async def create_postgresql_many(self, *, session: AsyncSession, objs: list[IN_SCHEMA]) -> list[ModelType] | None:
        insert_statement = (
            insert(self._model)
            .values([jsonable_encoder(obj=obj, exclude_unset=True, by_alias=False) for obj in objs])
            .returning(self._model)
        )
        # 生成SQL语句
        statement = select(self._model).from_statement(insert_statement).execution_options(populate_existing=True)
        # 执行原生SQL语句
        result: ChunkedIteratorResult = await session.execute(statement=statement)
        # 执行提交会话处理
        await session.commit()
        # 执行结果列表
        data: list[ModelType] | None = result.scalars().all()
        # 返回列表
        return data

    # async def create_many(self, in_schema: IN_SCHEMA) -> SCHEMA:
    #     pass
    # 最慢
    # datas = [i for i in range(10000)]
    # for item in datas:
    #     sql_data = User(name=item)
    #     session.add(sql_data)
    #     session.commit()
    # 较慢
    # objects = [User(name=str(i)) for i in range(10000)]
    # session.add_all(objects)
    # session.commit()
    # 比较快
    # objects = [User(name=str(i)) for i in range(10000)]
    # session.bulk_save_objects(objects)
    # session.commit()
    # 最快
    # engine.execute(
    #     Customer.__table__.insert(),
    #     [{"name": 'NAME ' + str(i)} for i in xrange(n)]
    # )
    # 从模型中返回
    # return self._schema.from_orm(entry)

    async def get_by_id(self, entry_id: UUID) -> SCHEMA:
        entry = await self._db_session.get(self._model, entry_id)
        if not entry:
            raise HTTPException(status_code=404, detail="Object not found")
        return self._schema.from_orm(entry)

    async def get_by_ids(
            self, *, list_ids: List[Union[UUID, str]]) -> Optional[List[ModelType]]:
        response = await self._db_session.execute(select(self._model).where(self._model.id.in_(list_ids)))
        return response.scalars().all()

    async def get_count(self) -> Optional[ModelType]:
        response = await self._db_session.execute(select(func.count()).select_from(select(self._model).subquery()))
        return response.scalar_one()

    async def get_multi(
            self,
            *,
            skip: int = 0,
            limit: int = 100,
    ) -> List[ModelType]:
        query = select(self._model).offset(skip).limit(limit).order_by(self._model.id)
        response = await self._db_session.execute(query)
        return response.scalars().all()

    async def get_by_id_select(self, *, session: AsyncSession, id: str | uuid.UUID) -> ModelType | None:
        statement = select(self._model).where(self._model.id == id)
        result: ChunkedIteratorResult = await session.execute(statement=statement)
        data: ModelType | None = result.scalar_one_or_none()
        return data

    async def read_multi(self) -> List[SCHEMA]:
        query = select(self._model)
        results = (await self._db_session.execute(query)).scalars()
        return [self._schema.from_orm(item) for item in results]

    async def update(self, *, id: str | uuid.UUID, obj: IN_SCHEMA) -> ModelType | None:
        values = jsonable_encoder(obj=obj, exclude_unset=True, by_alias=False)
        update_statement = (
            update(self._model)
            .where(self._model.id == id)
            .values(**values)
            .returning(self._model)
            .execution_options(synchronize_session="fetch")
        )
        statement = (
            select(self._model).from_statement(statement=update_statement).execution_options(populate_existing=True)
        )
        result: ChunkedIteratorResult = await self._db_session.execute(statement=statement)
        await self._db_session.commit()
        data: ModelType | None = result.scalar_one_or_none()
        return data

    async def changeStatus(self, id: Union[int, str], status: Union[int, str] = "1") -> int:
        """ 修改状态 """
        sql = update(self._model).where(self._model.id == id).values({"status": status})
        result = await self._db_session.execute(sql)
        await self._db_session.commit()
        # SQLAlchemy 中的 rowcount 是一个只读属性，可以用来获取上一个操作影响的行数。它可以用来确定查询是否有效，以及影响的行数。
        # SQLAlchemy中的rowcount与rowcounty的区别是，rowcount只返回受影响的行数，
        # 而rowcounty返回所有行的数量，不管是否受影响。
        # rowcount只能在execute()方法执行完毕后调用，而rowcounty可以立即调用。
        return result.rowcounty

    async def delete_by_id_delete(self, *, session: AsyncSession, id: str | uuid.UUID) -> CursorResult:
        delete_statement = delete(self._model).where(self._model.id == id)
        result: CursorResult = await session.execute(statement=delete_statement)
        await session.commit()
        return result

    async def update_by_id(
            self, entry_id: UUID, in_data: PAGINATED_SCHEMA
    ) -> SCHEMA:
        entry = await self._db_session.get(self._model, entry_id)
        if not entry:
            raise HTTPException(status_code=404, detail="Object not found")
        in_data_dict: dict = in_data.dict(exclude_unset=True)
        for _k, _v in in_data_dict.items():
            setattr(entry, _k, _v)
        await self._db_session.flush()
        return self._schema.from_orm(entry)

    async def delete_by_uuid(self, entry_id: UUID) -> None:
        entry = await self._db_session.get(self._model, entry_id)
        if not entry:
            raise HTTPException(status_code=404, detail="Object not found")
        await self._db_session.delete(entry)
        await self._db_session.flush()
        return

    async def remove_by_id(self, id: int) -> int:
        """ 通过 id 删除对象 """
        sql = delete(self._model).where(self._model.id == id)
        result = await self._db_session.execute(sql)
        await self._db_session.commit()
        # SQLAlchemy的rowcount属性实际上是一个只读属性，用于表示最近执行的SELECT语句返回的行数。它有助于检测SQL语句是否成功执行，以及确定查询结果中包含多少行。
        # SQLAlchemy中的rowcount与rowcounty的区别是，rowcount只返回受影响的行数，
        # 而rowcounty返回所有行的数量，不管是否受影响。
        # rowcount只能在execute()方法执行完毕后调用，而rowcounty可以立即调用。
        return result.rowcount

    async def remove_multi_by_ids(self, db: AsyncSession, ids: list):
        """ 同时删除多个对象 """
        id_list = [int(i) for i in ids]  # postgresql 字段类型限制
        sql = delete(self._model).where(self._model.id.in_(ids))
        result = await db.execute(sql)
        await db.commit()
        # SQLAlchemy的rowcount属性实际上是一个只读属性，用于表示最近执行的语句返回的行数。它有助于检测SQL语句是否成功执行，以及确定查询结果中包含多少行。
        # SQLAlchemy中的rowcount与rowcounty的区别是，rowcount只返回受影响的行数，
        # 而rowcounty返回所有行的数量，不管是否受影响。
        # rowcount只能在execute()方法执行完毕后调用，而rowcounty可以立即调用。
        return result.rowcoun

    async def get_paginated_list(self, limit: int, offset: int) -> PAGINATED_SCHEMA:
        result: Result = await self._db_session.execute(
            select(self._model).order_by(self._order_by).limit(limit).offset(offset)
        )
        entries = result.scalars()
        total_count: Result = await self._db_session.execute(
            select(func.count()).select_from(self._model)
        )
        return self._paginated_schema(
            total=total_count.scalar(),
            items=[self._schema.from_orm(entry) for entry in entries],
        )

    async def get_paginated(self, name: str, pageIndex: int = 1, pageSize: int = 10) -> List[ModelType]:
        """ 验证用户 """
        filed_name = self._model.__table__.c[name]
        if pageIndex == -1 and pageSize == -1:
            sql = select(self._model).order_by(desc(filed_name))
        else:
            sql = select(self._model).offset((pageIndex - 1) * pageSize).limit(pageSize).order_by(desc(filed_name))
        result = await self._db_session.scalars(sql)
        # await self._db_session.close()  # 释放会话
        return result.all()
